package modele;

import exceptions.DataSourceException;
import exceptions.EmployeNotFoundException;
import exceptions.LoginException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;

public class EmployeModel implements InterfaceEmployeModel {

    private DataSource ds;

    @Override
    public void setDataSource(DataSource ds) {
        this.ds = ds;
    }

    private Connection connect() throws DataSourceException {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            throw new DataSourceException();
        }
    }

    private void disconnect(Connection conn) throws DataSourceException {
        try {
            conn.close();
        } catch (SQLException e) {
            throw new DataSourceException();
        }
    }

    @Override
    public Employe get(String username) throws DataSourceException,
                                               EmployeNotFoundException {
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        Employe employe = null;

        try {
            conn = connect();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from employes where username=\'" + username + "\'");

            if (rs.next()) {
                employe = new Employe();
                employe.setUsername(rs.getString("username"));
                employe.setPassword(rs.getString("password"));
                employe.setDepid(Integer.parseInt(rs.getString("depid")));
                employe.setRoleid(Integer.parseInt(rs.getString("roleid")));
                employe.setName(rs.getString("name"));
                employe.setPhone(rs.getString("phone"));
                employe.setEmail(rs.getString("email"));
            } else
                throw new EmployeNotFoundException("Employe " + username + " non trouve!");
        } catch (SQLException e) {
            throw new DataSourceException();
        } finally {
            disconnect(conn);
        }

        return employe;
    }

    @Override
    public void create(Employe employe) throws DataSourceException {
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = connect();
            stmt = conn.createStatement();

            StringBuilder sqlString = new StringBuilder("insert into employes ");
            sqlString.append("values ('").append(employe.getUsername()).append("', ");
            sqlString.append("'").append(employe.getPassword()).append("', ");
            sqlString.append("'").append(employe.getName()).append("', ");
            sqlString.append(employe.getRoleid()).append(", ");
            sqlString.append("'").append(employe.getPhone()).append("', ");
            sqlString.append("'").append(employe.getEmail()).append("', ");
            sqlString.append(employe.getDepid()).append(")");

            stmt.execute(sqlString.toString());
        } catch (SQLException e) {
            throw new DataSourceException();
        } finally {
            disconnect(conn);
        }
    }

    @Override
    public List<Employe> getAll() throws DataSourceException {
        List employes = null;
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;

        try {
            conn = connect();
            stmt = conn.createStatement();

            rs = stmt.executeQuery("select * from employes, roles, "
                                   + "services where employes.roleid=roles.roleid "
                                   + "and employes.depid=services.depid");

            Employe employe;
            employes = new ArrayList<Employe>();

            while (rs.next()) {
                employe = new Employe();

                employe.setUsername(rs.getString("username"));
                employe.setName(rs.getString("name"));
                employe.setRolename(rs.getString("rolename"));
                employe.setPhone(rs.getString("phone"));
                employe.setEmail(rs.getString("email"));
                employe.setRoleid(new Integer(rs.getInt("roleid")));
                employe.setDepid(new Integer(rs.getInt("depid")));
                employe.setDepartment(rs.getString("depname"));

                employes.add(employe);
            }
        } catch (SQLException e) {
            throw new DataSourceException();
        } finally {
            disconnect(conn);
        }
        return employes;
    }

    @Override
    public void delete(String username) throws DataSourceException {
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = connect();
            stmt = conn.createStatement();

            StringBuilder sqlString = new StringBuilder("delete from employes ");
            sqlString.append("where username=\'").append(username).append("\'");

            stmt.execute(sqlString.toString());
        } catch (SQLException e) {
            throw new DataSourceException();
        } finally {
            disconnect(conn);
        }
    }

    @Override
    public void update(Employe employe) throws DataSourceException {
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = connect();
            stmt = conn.createStatement();

            StringBuilder sqlString = new StringBuilder("update employes ");
            sqlString.append("set password='").append(employe.getPassword()).append("', ");
            sqlString.append("roleid=").append(String.valueOf(employe.getRoleid())).append(", ");
            sqlString.append("name='").append(employe.getName()).append("', ");
            sqlString.append("phone='").append(employe.getPhone()).append("', ");
            sqlString.append("email='").append(employe.getEmail()).append("', ");
            sqlString.append("depid=").append(String.valueOf(employe.getDepid()));
            sqlString.append(" where username='").append(employe.getUsername()).append("'");

            stmt.execute(sqlString.toString());
        } catch (SQLException e) {
            throw new DataSourceException();
        } finally {
            disconnect(conn);
        }
    }

    @Override
    public String logon(String username, String password) throws
            LoginException, DataSourceException {
        String user = null;
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;

        try {
            conn = connect();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from employes where username=\'"
                                   + username + "\' "
                                   + "and password=\'" + password + "\'");

            if (rs.next())
                user = rs.getString("username");
            else
                throw new LoginException();
        } catch (SQLException e) {
            throw new DataSourceException();
        } finally {
            disconnect(conn);
        }

        return user;
    }
}